package org.ezplatform.office.questionnaire.service;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.persistence.Query;

import org.apache.commons.collections.CollectionUtils;
import org.ezplatform.cloud.api.component.attachment.AttachmentUtilsService;
import org.ezplatform.cloud.api.component.popuser.PopUserScopeUtilService;
import org.ezplatform.cloud.api.system.org.OrgApiService;
import org.ezplatform.cloud.api.system.user.UserApiService;
import org.ezplatform.component.permission.service.CmpApplyScopeService;
import org.ezplatform.core.common.WebUser;
import org.ezplatform.core.dao.jpa.JpaBaseDao;
import org.ezplatform.core.pagination.PropertyFilter;
import org.ezplatform.core.service.BaseService;
import org.ezplatform.core.web.util.WebUtils;
import org.ezplatform.office.questionnaire.dao.QuestionnaireAnswerDao;
import org.ezplatform.office.questionnaire.entity.Questionnaire;
import org.ezplatform.office.questionnaire.entity.QuestionnaireAnswer;
import org.ezplatform.office.questionnaire.entity.QuestionnaireTopic;
import org.ezplatform.office.questionnaire.entity.QusetionnaireAnswerStatus;
import org.ezplatform.util.SqlUtils;
import org.ezplatform.util.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.context.annotation.Lazy;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.Pageable;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

/** 
 * <br> 答卷信息接口类
 * <br> 创建时间 2017-11-23
 * @author wujs
 * @version 1.0
 *
*/
@Service("questionnaireAnswerService")
@Transactional
public class QuestionnaireAnswerService extends BaseService<QuestionnaireAnswer,String>{
	
	
	@Autowired
	@Qualifier("questionnaireAnswerDao")
	private QuestionnaireAnswerDao answerDao;
	
	@Autowired
	private UserApiService userApiService;
	@Lazy
	@Autowired
	private QuestionnaireAnswerService answerService;
	@Lazy
	@Autowired
	private QuestionnaireService questionnaireService;
	
	@Autowired
	private AttachmentUtilsService attachmentUtilsService;

//	@Autowired
//	private QuestionnaireTopicService topicService;
	
	@Autowired
	private PopUserScopeUtilService popScopeService;
	
	@Autowired
	private CmpApplyScopeService cmpApplyScopeService;
	
	@Autowired
	private OrgApiService orgApiService;

//	@Autowired
//	private RoleApiService roleApiService;
	
	@Override
	protected JpaBaseDao<QuestionnaireAnswer, String> getEntityDao() {
		return answerDao;
	}
	
	/**
	 * 答卷列表
	 * @param pageable 系统参数：分页参数
	 * @param propertyFilter 系统参数：页面参数，如：搜索字段
	 * @param token 系统参数：TOKEN
	 * @return 答卷列表分页数据
	 */
	public Page<Map> getAnswerMap(Pageable pageable,List<PropertyFilter> propertyFilter,String status,String userid,boolean isSuperAdmin,String token){
		WebUser user=WebUtils.getCurrentUser();
		
		/*获取当前登录用户的组织岗位*/
		String userId = WebUtils.getCurrentUser().getUserId();
		String corpId = WebUtils.getCurrentUser().getCorpId();
		
		List<Map> userL=this.findByListNativeQuery("select user_status as userStatus,is_delete as isDelete from sys_user where id='"+userId+"'", "");
		if(userL!=null && userL.size()>0) {
			Map userStatus=userL.get(0);
			if("offDuty".equals(userStatus.get("userStatus"))||"1".equals(userStatus.get("isDelete")+"")) {
				return new PageImpl<Map>(new ArrayList<Map>());
			}
		}
		Map<String,Object> paramMap = new HashMap<String,Object>();
		paramMap.put("corpId", corpId);
		paramMap.put("createdBy", userId);
		String sql ="SELECT oq.id, oqas.id status_id, oq.name, oq.type, oq.start_date AS startDate, oq.end_date AS endDate, oq.status, oqas.status answerStatus,oq.createdDate,oq.publish,oq.close_date as closeDate,oq.no_limit as noLimit,oqas.answer_num as answerNum,oqas.grade,oq.pass_score as passScore,oq.answer_num as answerNumSet "
				+ " FROM oa_questionnaire oq LEFT JOIN (SELECT id,status,questionnaire_id,answer_num,grade from  oa_qusetionnaire_answer_status where createdBy =:createdBy) oqas ON oqas.questionnaire_id = oq.ID  WHERE oq.corp_id = :corpId ";
		sql += " and ((oq.publish = " + Questionnaire.PublishEnum.PUBLISH.getCode();
		if(StringUtils.isNotBlank(status)){
			//如果状态是未答卷，则需要判断状态为空或者状态为0
			if(StringUtils.equals(
					QusetionnaireAnswerStatus.StatusEnum.NOANSWER.getCode(),
					status)){
				sql += " and (oqas.status = :status or oqas.status is null) ";
			}else{
				sql += " and oqas.status = :status ";
			}
			paramMap.put("status", status);
		}
		sql += ") ";
		if(StringUtils.isNotBlank(status)) {
			if(status.equals("1")) {
				sql +=" or (oq.publish = 2 and oqas.status = 1)";
			}
		}else {
			sql +=" or (oq.publish = 2 and oqas.status = 1)";
		}
		//and oq.close_date>= fn_get_sys_date()
		sql +=" ) and ((oqas.status = 1 or oqas.status = 4) or ((oqas.status is null or (oqas.status != 1 and oqas.status != 4))))";
		String scopeSql = this.cmpApplyScopeService.getInScopeDataSql(userId, "oa_questionnaire_scope", "oa_questionnaire", "questionnaire_scope", "oq", paramMap);
		//List<String> list =orgApiService.getOrgIdsByUserIds(userId,corpId,"","2",token);
		List<String> list = this.cmpApplyScopeService.getAllOrgStaGrpIdsByUserId(userId);
		paramMap.put("OrgStaGrpIds", list);
		sql += " and " + scopeSql;
		sql ="select * from ("+sql+") t where 1=1 ";
		sql = PropertyFilter.getSqlByFilterAndPageable("t",sql,"t.createdDate desc", propertyFilter,pageable);
		Page<Map> page = this.findByPageNativeSQL(pageable, sql,paramMap);
		return page;
	}
	
	/**
	 * 答卷统计列表
	 * @param pageable 系统参数：分页参数
	 * @param propertyFilter 系统参数：页面参数，如：搜索字段
	 * @param quesitionnaireId 问卷ID
	 * @param searchName 搜索字段
	 * @param token 系统参数：TOKEN
	 * @return 答卷统计列表分页数据
	 */
	public Page<Map> getStatisticsAnswerInfo(Pageable pageable,List<PropertyFilter> propertyFilter,String quesitionnaireId, 
			String searchName, String departmentName,String status, String token, String userId){
		String corpId =WebUtils.getCurrentUser().getCorpId();
		Map<String,Object> paramMap = new HashMap<String,Object>();
		paramMap.put("quesitionnaireId", quesitionnaireId);
		paramMap.put("corpId", corpId);
		String sql ="select t.id, t.userName,t.orgName as orgUnitName,t.account,t.orgNamePath,t.createdBy,t.type,"
				+ "t.createdDate,t.publish , t.status, t.quesCreatedBy, '" + userId + "' as user_id "
//				+ ",t.fraction,t.endDate "
				+ "from (SELECT oqas.id,su.user_name userName,su.login_name as account,so.org_name_path as orgNamePath, so.org_name orgName,"
//				+ "oq.end_date as endDate, "
				+ " oq.publish,oq.type,"
				+ "oqas.createdBy, oqas.createdDate, oqas.status, oq.createdBy quesCreatedBy "
//				+ ",( SELECT sum(fraction) FROM oa_questionnaire_answer "
//				+ "WHERE questionnaire_id = oqas.questionnaire_id and createdBy = oqas.createdBy and topic_id in (select id from oa_questionnaire_topic where questionnaire_id = oqas.questionnaire_id and is_score ='0')) fraction "
				+ "FROM oa_qusetionnaire_answer_status oqas LEFT JOIN sys_user su "
				+ "ON su.id = oqas.createdBy AND corp_id = :corpId "
//				+ "LEFT JOIN sys_org_user sou  ON sou.user_id = su.id and sou.isMain='1' "
//				+ "LEFT JOIN sys_org so ON so.id = sou.org_id "
				+ "LEFT JOIN sys_org so ON so.id = oqas.org_id "
				+" left join oa_questionnaire oq on oqas.questionnaire_id = oq.id "
				+ "WHERE oqas.questionnaire_id = :quesitionnaireId  and oqas.status in (1,3, 4) "
				+ ") t where 1=1 ";
		if(StringUtils.isNotBlank(StringUtils.null2String(searchName))){
			sql += " and t.userName like :userName";
			paramMap.put("userName", "%"+searchName+"%");
		}
		if(StringUtils.isNotBlank(StringUtils.null2String(departmentName))){
			sql += " and t.orgName like :departmentName";
			paramMap.put("departmentName", "%"+departmentName+"%");
		}
		if(StringUtils.isNotBlank(StringUtils.null2String(status))){
			if("1".equals(status)) {
				sql += " and t.status in (1,4)";
			}else {
				sql += " and t.status = :status";
				paramMap.put("status", status);
			}
			
		}
		sql+=" ORDER BY t.createdDate DESC";
		Page<Map> page = this.findByPageNativeSQL(pageable, sql,paramMap);
//		Date endDate ;
//		Date nowDate = new Date() ;
//		for(Map con : page.getContent()){
//			endDate = (Date)con.get("endDate");
//			con.put("endDate",endDate.getTime());
//		    con.put("nowdate",nowDate.getTime());
//		}
		return page;
	}
	
	
	/**
	 * 未答卷统计列表
	 * @param pageable 系统参数：分页参数
	 * @param propertyFilter 系统参数：页面参数，如：搜索字段
	 * @param quesitionnaireId 问卷ID
	 * @param searchName 搜索字段
	 * @param token 系统参数：TOKEN
	 * @return 答卷统计列表分页数据
	 */
	public Page<Map> getStatisticsNoAnswerInfo(Pageable pageable, List<PropertyFilter> propertyFilter,String quesitionnaireId, 
			String searchName, String departmentName, String token, String corpId){
		Map<String,Object> paramMap = new HashMap<String,Object>();
		Questionnaire entity = questionnaireService.findOne(quesitionnaireId);
		String questionnaireScope = entity.getQuestionnaireScope();
		StringBuffer noAnwserSql = new StringBuffer();
		noAnwserSql.append("select su.id,su.user_name as userName,so.org_name as orgName,su.login_name as account,so.org_name_path as orgNamePath from sys_user su");
		noAnwserSql.append(" left join sys_org_user sou on sou.user_id = su.id");
		noAnwserSql.append(" left join sys_org so on so.id = sou.org_id where");
		if(StringUtils.isNotBlank(questionnaireScope)) {
			List<String> userList = getQuestionnaireUserIds(quesitionnaireId, corpId, token);
			if(userList.size() > 0) {
				paramMap.put("userList", userList);
				noAnwserSql.append(" su.id in (:userList) " );
			}else {
				noAnwserSql.append(" su.id in ('') " );
			}
		}else {// 默认所有人
			paramMap.put("corpId", corpId);
			noAnwserSql.append(" su.is_delete=0 and su.corp_id=:corpId ");
		}
		noAnwserSql.append(" and su.id not in (select oqas.createdBy from oa_qusetionnaire_answer_status oqas ");
		noAnwserSql.append(" where oqas.questionnaire_id = '"+quesitionnaireId+"' and oqas.status in (1,3, 4)) and sou.isMain = 1");
		if(StringUtils.isNotBlank(StringUtils.null2String(searchName))){
			noAnwserSql.append(" and su.user_name like :searchName");
			paramMap.put("searchName", "%"+searchName+"%");
		}
		if(StringUtils.isNotBlank(StringUtils.null2String(departmentName))){
			noAnwserSql.append(" and so.org_name like :departmentName");
			paramMap.put("departmentName", "%"+departmentName+"%");
		}
		String sql2 = PropertyFilter.getSqlByFilterAndPageable("su",noAnwserSql.toString(),"su.user_name", propertyFilter,pageable);
		Page<Map> page = this.findByPageNativeSQL(pageable, sql2, paramMap);
		return page;
	}
	
	public List<String> getQuestionnaireUserIds(String quesitionnaireId, String corpId, String token){
		List<String> userList = new ArrayList<String>();
		String sql = "select oqs.scopeType,oqs.scopeValue from oa_questionnaire_scope oqs"+
				" left join oa_questionnaire oq on oq.questionnaire_scope = oqs.fieldDataValue"+
				" where oq.id = '"+quesitionnaireId+"'";
		List<Map> scopelist = super.findByListNativeQuery(sql, "",new HashMap());
		if(CollectionUtils.isNotEmpty(scopelist)) {
			for(Map map : scopelist) {
				if(map.get("scopeType").toString().equals("user")) {
					userList.add(map.get("scopeValue").toString());
				}
				if(map.get("scopeType").toString().equals("org")) {
					List<String> orgUserIdList = userApiService.getUserIdsByOrgId(map.get("scopeValue").toString(), "", "1", corpId, token);
					for(String userId : orgUserIdList) {
						userList.add(userId);
					}
				}
			}
		}
		return userList;
	}
	
	/**
	 * 移动端未答卷统计列表
	 * @param pageSize 
	 * @param page 
	 * @param search 
	 * @param corpId 
	 * @param quesId 
	 * @param pageable 系统参数：分页参数
	 * @param propertyFilter 系统参数：页面参数，如：搜索字段
	 * @param quesitionnaireId 问卷ID
	 * @param searchName 搜索字段
	 * @param token 系统参数：TOKEN
	 * @param search2 
	 * @return 答卷统计列表分页数据
	 */
	public List<Map> getStatisticsNoAnswerInfoMB(String quesId, String corpId, String search, String page, String pageSize,String token, String search2,String orgId){
		int pageSizeInt = Integer.parseInt(pageSize);
		int start = (Integer.parseInt(page) - 1) * pageSizeInt;
		Map<String,Object> paramMap = new HashMap<String,Object>();
		Questionnaire entity = questionnaireService.findOne(quesId);
		String questionnaireScope = entity.getQuestionnaireScope();
		StringBuffer noAnwserSql = new StringBuffer();
		noAnwserSql.append("select su.id,su.user_name as userName,so.org_name as orgName from sys_user su");
		noAnwserSql.append(" left join sys_org_user sou on sou.user_id = su.id");
		noAnwserSql.append(" left join sys_org so on so.id = sou.org_id where");
		if(StringUtils.isNotBlank(questionnaireScope)) {
			List<String> userList = getQuestionnaireUserIds(quesId, corpId, token);
			if(userList.size() > 0) {
				paramMap.put("userList", userList);
				noAnwserSql.append(" su.id in (:userList) " );
			}else {
				noAnwserSql.append(" su.id in ('') " );
			}
		}else {// 默认所有人
			paramMap.put("corpId", corpId);
			noAnwserSql.append(" su.is_delete=0 and su.corp_id=:corpId ");
		}
		noAnwserSql.append(" and su.id not in (select oqas.createdBy from oa_qusetionnaire_answer_status oqas ");
		noAnwserSql.append(" where oqas.questionnaire_id = '" + quesId + "' and oqas.status in (1,3, 4)) and sou.isMain = 1");
		if(StringUtils.isNotBlank(search)) {
			noAnwserSql.append(" and su.user_name like '%"+search+"%'");
		}
		if(StringUtils.isNotBlank(orgId)){
			noAnwserSql.append(" and so.id in (:orgId)");
			String[] orgIds = orgId.split(",");
			List<String> orgIdList = Arrays.asList(orgIds);
			paramMap.put("orgId",orgIdList);
		}
		noAnwserSql.append(" order by su.user_name");
		List<Map> list = super.findByListNativeQuery(noAnwserSql.toString(), null, paramMap, start, pageSizeInt);
		if(CollectionUtils.isNotEmpty(list)) {
			String userIds = "";
			for(int i = 0; i < list.size(); i++) {
				if(i == list.size() - 1) userIds += list.get(i).get("id");
				else userIds += list.get(i).get("id") + ",";
			}
			Map<String, String> images = getImages(userIds, corpId, token);
			for(Map m : list){
				m.put("closeDate", entity.getCloseDate());
				String image = images.get(m.get("id"));
				if(StringUtils.isNotBlank(image)) m.put("image", image);
				else m.put("image", "");
			}
		}
		return list;
	}
	
	/**
	 * 未答卷 用户id列表
	 * @param quesitionnaireId 问卷ID
	 * @param departmentName 
	 * @param userName 
	 * @param token 
	 * @return  
	 */
	public List<String> getStatisticsNoAnswerInfo( String quesitionnaireId, String userName, String departmentName, String token ){
//		String corpId =WebUtils.getCurrentUser().getCorpId();
//		Map<String,Object> paramMap = new HashMap<String,Object>();
//		paramMap.put("quesitionnaireId", quesitionnaireId);
//		paramMap.put("corpId", corpId);
//		String sql ="select t.user_id from ( " + 
//				"select su.id user_id,su.user_name userName, so.org_name orgName,so.id org_id  " + 
//				"from oa_questionnaire_scope oqs  " + 
//				"LEFT JOIN sys_user su on su.id = oqs.scopeValue AND corp_id = :corpId  " + 
//				"LEFT JOIN sys_org_user sou on su.id = sou.user_id " + 
//				"LEFT JOIN sys_org so ON so.id = sou.org_id   " + 
//				"where oqs.fieldDataValue = ( select oq.questionnaire_scope from oa_questionnaire oq where oq.id= :quesitionnaireId ) " + 
//				"and  oqs.scopeValue not in ( select oqas.createdBy from oa_qusetionnaire_answer_status oqas " + 
//				"where oqas.status =1 and oqas.questionnaire_id = :quesitionnaireId ) " + 
//				"ORDER BY so.org_name  ,su.user_name  desc " + 
//				") t where 1=1 ";
//		Query localQuery = getEntityManager().createNativeQuery(sql.toString());
//		SqlUtils.setQueryParameter(paramMap, localQuery);
//		@SuppressWarnings("unchecked")
//		List<String> localList = localQuery.getResultList();
//		
//		return localList;
		String corpId =WebUtils.getCurrentUser().getCorpId();
		Map<String,Object> paramMap = new HashMap<String,Object>();
		String sql = "select oqs.scopeType,oqs.scopeValue from oa_questionnaire_scope oqs"+
				" left join oa_questionnaire oq on oq.questionnaire_scope = oqs.fieldDataValue"+
				" where oq.id = '"+quesitionnaireId+"'";
		List<Map> scopelist = super.findByListNativeQuery(sql, "",new HashMap());
		List<String> userList = new ArrayList<String>();
		if(CollectionUtils.isNotEmpty(scopelist)) {
			for(Map map : scopelist) {
				if(map.get("scopeType").toString().equals("user")) {
					userList.add(map.get("scopeValue").toString());
				}
				if(map.get("scopeType").toString().equals("org")) {
					List<String> orgUserIdList = userApiService.getUserIdsByOrgId(map.get("scopeValue").toString(), "", "1", corpId, token);
					for(String userId : orgUserIdList) {
						userList.add(userId);
					}
				}
			}
		}
		String noAnwserSql = "select su.id from sys_user su"
			+ " left join sys_org_user sou on sou.user_id = su.id"
			+ " left join sys_org so on so.id = sou.org_id"
			+ " where su.id not in (select oqas.createdBy from oa_qusetionnaire_answer_status oqas "
			+ " where oqas.questionnaire_id = '"+quesitionnaireId+"' and oqas.status in (1,3)) and sou.isMain = 1";
		if(CollectionUtils.isNotEmpty(userList)) {
			noAnwserSql += " and su.id in (:userList)";
			paramMap.put("userList", userList);
		}
		if(!"".equals(StringUtils.null2String(userName))){
			noAnwserSql += " and su.user_name like '%"+userName+"%'";
		}
		if(!"".equals(StringUtils.null2String(departmentName))){
			noAnwserSql += " and so.org_name like '%"+departmentName+"%'";
		}
		Query localQuery = getEntityManager().createNativeQuery(noAnwserSql.toString());
		SqlUtils.setQueryParameter(paramMap, localQuery);
		List<String> localList = localQuery.getResultList();
		return localList;
	}
	
	
	/**
	 * 获取答卷信息
	 * @param userId 用户id
	 * @param optionId 选项id
	 * @return 答卷信息实体
	 */
	public QuestionnaireAnswer getAnswerInfo(String userId,String optionId){
		return answerDao.getAnswerInfo(userId, optionId);
	}
	
	/**
	 * 删除用户答卷
	 * @param userId 用户id
	 * @param quesId 问卷ID
	 * @return 删除成功返回true
	 */
	public boolean deleteAnswerByQuesId(String userId,String quesId){
		answerDao.deleteAnswerByQuesId(userId,quesId);
		return true;
	}
	
	/**
	 * 获取提交答卷中选项个数
	 * @param optionId 选项id
	 * @return 提交答卷中选项个数
	 */
	public int getOptionNum(String optionId){
		int num=0;
		String sql = "select * from oa_questionnaire_answer oqa where  oqa.user_id in (select createdBy from oa_qusetionnaire_answer_status where STATUS='1' and questionnaire_id = oqa.questionnaire_id) and oqa.questionnaire_id in (select questionnaire_id from oa_qusetionnaire_answer_status where STATUS='1') and oqa.option_id=?1";
		List<String> list =  this.getEntityManager().createNativeQuery(sql).setParameter(1, optionId).getResultList(); 
		if(list !=null){
			num = list.size();
		}
		return num;
	}
	
	/**
	 * 获取提交答卷中选项答案个数
	 * @param optionId 选项ID
	 * @param optionOrder 答案
	 * @return 提交答卷中选项答案个数
	 */
	public int getMulScaleOptionNum(String optionId,String optionOrder){
		int num=0;
		String sql = "select oqa.id,oqa.answer from oa_questionnaire_answer oqa where  oqa.user_id in (select createdBy from oa_qusetionnaire_answer_status where STATUS='1' and questionnaire_id = oqa.questionnaire_id) and oqa.questionnaire_id in (select questionnaire_id from oa_qusetionnaire_answer_status where STATUS='1') and oqa.option_id='"+optionId+"'";// and answer ='" + optionOrder + "'";
		List<Map> list =  super.findByListNativeQuery(sql,"");
//				this.getEntityManager().createNativeQuery(sql).setParameter(1, optionId).setParameter(2, optionOrder).getResultList();
		if(CollectionUtils.isNotEmpty(list)){
			for(Map map : list){
			    if(StringUtils.equals(optionOrder,StringUtils.null2String(map.get("answer")))){
					num ++;
				}
			}
		}
//		if(list !=null){
//			num = list.size();
//		}
		return num;
	}
	
	/**
	 * 根据问卷id删除答卷
	 * @param list 问卷ID集合
	 * @return 删除答卷个数
	 */
	public int deleteAnswerByQuesId(String id){
		return answerDao.deleteAnswerByQuesId(id);
	}
	
	/**
	 * 根据问卷id删除答卷
	 * @param list 问卷ID集合
	 * @return 删除答卷个数
	 */
	public int deleteAnswerByQuesId(List<String> list){
		return answerDao.deleteAnswerByQuesId(list);
	}


	/**
	 *	查询问卷下 的答案
	 * @param quesId
	 * @return
	 */
	public List<QuestionnaireAnswer> getAnswerByQuesId(String quesId){
		return answerDao.getAnswerByQuesId(quesId);
	}
	
	/**
	 * 获取问卷答卷总分数
	 * @param questId 问卷id
	 * @return 问卷答卷总分数
	 */
	public double getAllAnswerScore(String questId){
		String sql ="select SUM(oqa.fraction) from oa_questionnaire_answer oqa where oqa.questionnaire_id =:questId and oqa.questionnaire_id in (select oqas.questionnaire_id from oa_qusetionnaire_answer_status oqas where oqas.status='1') and oqa.topic_id in (select id from oa_questionnaire_topic where is_score = '0' and questionnaire_id = :questId)";
		Map<String,Object> paramMap = new HashMap<String,Object>();
		paramMap.put("questId", questId);
		Query query = this.getEntityManager().createNativeQuery(sql);
   		SqlUtils.setQueryParameter(paramMap, query);
		List<Double> list =  query.getResultList();
		list.removeAll(Collections.singleton(null));
		if(list == null || list.size()==0 ){
			return 0;
		}
		return Double.parseDouble(StringUtils.null2String(list.get(0)));
	}
	
	/**
	 * 获取题目答案分数
	 * @param topicId 题目ID
	 * @return 题目答案分数
	 */
	public double getTopicAnswerScore(String topicId){
		String sql ="select SUM(oqa.fraction) from oa_questionnaire_answer oqa where oqa.topic_id =:topicId and oqa.questionnaire_id in (select oqas.questionnaire_id from oa_qusetionnaire_answer_status oqas where oqas.status='1') and oqa.topic_id in (select id from oa_questionnaire_topic where is_score = '0' and id = :topicId)";
		Map<String,Object> paramMap = new HashMap<String,Object>();
		paramMap.put("topicId", topicId);
		Query query = this.getEntityManager().createNativeQuery(sql);
   		SqlUtils.setQueryParameter(paramMap, query);
		List<Double> list =  query.getResultList();
		list.removeAll(Collections.singleton(null));
		if(list == null || list.size()==0){
			return 0;
		}
		return Double.parseDouble(StringUtils.null2String(list.get(0)));
	}
	
/*=============================================钉钉方法=======================================*/	
	
	
	/**
	 * 答卷列表
	 * @param token 系统参数：TOKEN
	 * @param token
	 * @return 答卷列表分页数据
	 */
	public List<Map> getAnswerList(String quesType,String corpId,String userId,String token,
			String page, String pageSize,String search,String status,String startDate,String endDate){
		int pageSizeInt = Integer.parseInt(pageSize);
		int start = (Integer.parseInt(page)-1)*pageSizeInt;
		/*获取当前登录用户的组织岗位*/
		List<String> list = orgApiService.getOrgIdsByUserIds(userId,corpId,"","2",token);
		list.add(userId);
		List<String> scopeList = popScopeService.getFieldDataValue("oa_questionnaire_scope", list, "oa_questionnaire", "questionnaire_scope");
		if(scopeList == null || scopeList.size()==0){
			scopeList.add("-999");
		}
		StringBuffer sql =new StringBuffer("SELECT oq.id, oqas.id statusId, oq.name, oq.type, oq.start_date AS startDate, oq.end_date AS endDate,oq.no_limit as noLimit, oq.publish, oqas.status answerStatus,oq.createdDate,oqas.answer_num as answerNum,oqas.grade,oq.pass_score as passScore,oq.answer_num as answerNumSet,s.user_name as createdByName FROM oa_questionnaire oq LEFT JOIN (SELECT id,status,questionnaire_id,answer_num,grade from  oa_qusetionnaire_answer_status where createdBy =:createdBy) oqas ON oqas.questionnaire_id = oq.ID  left join sys_user s on s.id=oq.createdBy  WHERE oq.corp_id = :corpId ");
//		if(type.equals("0")){//待答
//			String currentDate = DateUtils.formatDateTime(new Date());
//			sql.append(" and (oqas.status in ('0','2') or oqas.status is null ) ");
//			sql.append(" and (oq.status = '1' or (oq.status = '2' and oq.start_date<='"+currentDate+"' and oq.end_date>='"+currentDate+"')) ");
//		}else if(type.equals("1")){//所有
//
//		}
		//时间筛选
		if(StringUtils.isNotBlank(startDate) ){
			sql.append(" and fn_date_format(oq.createdDate,'yyyy-MM-dd') >= '" + startDate + "'");
		}
		if(StringUtils.isNotBlank(endDate)){
			sql.append(" and fn_date_format(oq.createdDate,'yyyy-MM-dd') <= '" + endDate + "'");
		}
		//答卷类型筛选
		if(StringUtils.isNotBlank(quesType)){
			sql.append(" and oq.type = '"+ quesType + "'");
		}
		sql.append(" and ((oq.publish = " + Questionnaire.PublishEnum.PUBLISH.getCode());
		//问卷答卷状态
		if(StringUtils.isNotBlank(status)){
			//当问卷为0或者为空，表示未答卷
			if(StringUtils.equals(status, QusetionnaireAnswerStatus.StatusEnum.NOANSWER.getCode())){
				sql.append(" and (oqas.status = "+ QusetionnaireAnswerStatus.StatusEnum.NOANSWER.getCode() + " or oqas.status is null )");
			}else{
				sql.append(" and oqas.status = "+ status);
			}
		}
		sql.append(" )");
		if(StringUtils.isNotBlank(status)) {
			if(status.equals("1")) {
				sql.append(" or (oq.publish = 2 and oqas.status = 1)");
			}
		}else {
			sql.append(" or (oq.publish = 2 and oqas.status = 1)");
		}
		sql.append(" )");
		if(StringUtils.isNotBlank(search)){
			search = search.replaceAll("_","\\\\_");
			sql.append(" and oq.name like '%" + search + "%'" );
		}
		sql.append(" and ((oqas.status = 1 or oqas.status = 4) or ((oqas.status is null or (oqas.status != 1 and oqas.status != 4)) and oq.close_date>= fn_get_sys_date()))");
		sql.append(" and (oq.questionnaire_scope in (:scopeList) or oq.questionnaire_scope='' or oq.questionnaire_scope is null) order by oq.createdDate desc");
//		
//		Query query = this.getEntityManager().createNativeQuery(sql.toString()).setParameter("scopeList", scopeList).setParameter("corpId", corpId).setParameter("createdBy", userId);
//		query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
//		query.setFirstResult(start);
//	    query.setMaxResults(pageSizeInt);
//		List<Map> r = query.getResultList();
		Map paramMap = new HashMap();
		paramMap.put("scopeList", scopeList);
		paramMap.put("corpId", corpId);
		paramMap.put("createdBy", userId);
		List<Map> r = super.findByListNativeQuery(sql.toString(),"",paramMap,start,pageSizeInt);
		return r;
	}


	/**
	 * 手机端查询问卷提交的人员列表
	 * @param quesId
	 * @param corpId
	 * @param search
	 * @param status
	 * @param startDate   yyyy-mm-dd
	 * @param endDate
	 * @param page
	 * @param pageSize
	 * @return
	 */
	public List<Map> getStatisticsAnswerList2Mobile(String quesId,String corpId,String userId,
			String search,String orgId,String page,String pageSize,String isAdmin,String token){
		int pageSizeInt = Integer.parseInt(pageSize);
		int start = (Integer.parseInt(page)-1)*pageSizeInt;
		Map<String,Object> paramMap = new HashMap<>();
		paramMap.put("quesId",quesId);
		paramMap.put("corpId",corpId);
		StringBuffer sql = new StringBuffer();
		sql.append(" select t.id, t.userName,t.orgName as orgUnitName,t.createdBy,t.createdDate,t.publish ,");
		sql.append(" t.status,t.quesId, t.queCreatBy,'" + userId + "' as user_id, " + isAdmin + " as isAdmin from ");
		sql.append(" (SELECT oqas.id,su.user_name userName, so. org_name orgName, oq.publish,oqas.createdBy,");
		sql.append(" oq.createdBy queCreatBy, oqas.createdDate , oqas.status,oqas.questionnaire_id as quesId FROM oa_qusetionnaire_answer_status oqas ");
		sql.append(" LEFT JOIN sys_user su ON su.id = oqas.createdBy AND corp_id = :corpId ");
		sql.append(" LEFT JOIN sys_org_user ou ON ou.user_id = su.id and ou.isMain='1'");
		sql.append(" LEFT JOIN sys_org so ON so.id = ou.org_id  ");
		sql.append(" left join oa_questionnaire oq on oqas.questionnaire_id = oq.id ");
		sql.append(" WHERE oqas.questionnaire_id = :quesId  and oqas.status in (1,3,4)");
		if(StringUtils.isNotBlank(orgId)){
			sql.append(" and oqas.org_id in (:orgId)");
			String[] orgIds = orgId.split(",");
			List<String> orgIdList = Arrays.asList(orgIds);
			paramMap.put("orgId",orgIdList);
		}
		sql.append(" ) t where 1=1 ");
		if(StringUtils.isNotBlank(search)){
			search = search.replaceAll("_","\\\\_");
			sql.append(" and t.userName like '%" + search + "%'" );
		}
		sql.append(" ORDER BY t.createdDate DESC");
		List<Map> list = super.findByListNativeQuery(sql.toString(),"",paramMap,start,pageSizeInt);
		if(CollectionUtils.isNotEmpty(list)) {
			String userIds = "";
			for(int i = 0; i < list.size(); i++) {
				if(i == list.size() - 1) userIds += list.get(i).get("createdBy");
				else userIds += list.get(i).get("createdBy") + ",";
			}
			Map<String, String> images = getImages(userIds, corpId, token);
			for(Map m : list){
				String image = images.get(m.get("createdBy"));
				if(StringUtils.isNotBlank(image)) m.put("image", image);
				else m.put("image", "");
			}
		}
		return list;
	}
		
	/**
	 * 获取人有头像
	 * @param userId
	 * @param corpId
	 * @param token
	 * @return
	 */
	public Map<String, String> getImages(String userIds, String corpId, String token) {
		Map<String, String> map = new HashMap<>();
		List<Map<String,Object>> userList = this.userApiService.getUserList(userIds, corpId, "", token);
		if(CollectionUtils.isNotEmpty(userList)) {
			for(Map<String,Object> userMap : userList) {
				String fileName = StringUtils.null2String(userMap.get("imgId"));
				if (!"".equals(fileName)) {
					Map<String, String> image = this.attachmentUtilsService.getAttachmentByFileName(fileName, token);
					if (image != null && !image.isEmpty()) {
						map.put(StringUtils.null2String(userMap.get("id")), image.get("id"));
					}
				}
			}
		}
		return map;
	}

	/**
	 * 手机端根据题目查询答题详情
	 * @param quesId
	 * @param quesType
	 * @param topicId
	 * @return
	 */
	public List<Map> getAnswerDetail(String quesId,String topicType,String topicId,String token,String corpId){
		Map<String,Object> paramMap = new HashMap<String,Object>();
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT oa.id,oa.createdBy,oa.questionnaire_id,oa.user_id,oa.topic_id,");
		sql.append(" oa.option_id,oa.fraction,oqto.name,oa.answer,oa.answer_ext,su.user_name ");
		sql.append(" FROM oa_questionnaire_answer oa ");
		sql.append(" left join oa_questionnaire_topic_options oqto on oqto.id = oa.option_id ");
		sql.append(" left join oa_qusetionnaire_answer_status oqas on oa.questionnaire_id = oqas.questionnaire_id ");
		sql.append(" and oa.user_id = oqas.createdBy");
		sql.append(" left join sys_user su on su.id = oqas.createdBy ");
		sql.append(" where oa.topic_id = :topicId and oa.questionnaire_id = :quesId");
		sql.append(" and oqas.status='1'");
		paramMap.put("topicId", topicId);
		paramMap.put("quesId", quesId);
		List<Map> list = super.findByListNativeQuery(sql.toString(), null, paramMap);
		//填空题
		if(StringUtils.isNotBlank(topicType) && CollectionUtils.isNotEmpty(list)){
			if(StringUtils.equals(topicType,QuestionnaireTopic.TopicTypeEnum.FILLINBLANKS.getCode())){
				List<Map> returnList = new ArrayList<>();
				//插入标志位
				int i = 0;
				Map<String, Object> userIdMap = null;
				Map<String,Integer> userIdIndex = new HashMap<>();
				String userId = "";
				for(Map map : list){
					userId = StringUtils.null2String(map.get("user_id"));
					float fraction = Float.parseFloat(StringUtils.null2String(map.get("fraction")));
					if(userIdIndex.get(userId) != null){
						int index = userIdIndex.get(userId);
						Map mapIndex = returnList.get(index);
						if(map.get("answer")!=null&&!"".equals(map.get("answer").toString())&&!"无".equals(map.get("answer").toString())) {
							mapIndex.put("answer",mapIndex.get("answer")+";" + map.get("name")+":"+map.get("answer"));
						}else {
							mapIndex.put("answer",mapIndex.get("answer")+";" + map.get("name")+":"+"无");
						}
						returnList.set(index,mapIndex);
						userIdMap.put("fraction", fraction + Float.parseFloat(StringUtils.null2String(userIdMap.get("fraction"))));
					}else{
						userIdMap = new HashMap<>();
						if(map.get("answer")!=null&&!"".endsWith(map.get("answer").toString())) {
							userIdMap.put("answer",map.get("name")+":"+map.get("answer"));
						}else {
							userIdMap.put("answer",map.get("name")+":"+"无");
						}
						userIdMap.put("userId",userId);
						userIdMap.put("user_name",StringUtils.null2String(map.get("user_name")));
						returnList.add(userIdMap);
						userIdIndex.put(userId,i);
						userIdMap.put("fraction", fraction);
						i ++;
					}
				}
				if(userIdMap != null) 
				return returnList;
			}
			if(StringUtils.equals(topicType,QuestionnaireTopic.TopicTypeEnum.ANSWER.getCode()) || "shortAnswer".equals(topicType)){
				return list;
			}
		}
		if(StringUtils.equals(topicType,QuestionnaireTopic.TopicTypeEnum.FIELD.getCode())){
			List<Map> userList = answerService.getMBAnswerDetail(quesId, topicType, topicId);
			if(CollectionUtils.isNotEmpty(userList)) {
				for(Map map : userList) {
					List<Map> fieldAnswerList = answerService.getOptionAndAnswer(map.get("userId").toString(),topicId);
					if(CollectionUtils.isNotEmpty(fieldAnswerList)) {
						List fileInfo = new ArrayList();
						for(Map map1 : fieldAnswerList) {
							String[] attachmentId = map1.get("answer").toString().split("\\|");
							if (attachmentId!=null&&attachmentId.length>0) {
								for(int i=0;i<attachmentId.length;i++) {
									List file = attachmentUtilsService.getAttachmentByIds(attachmentId[i], corpId, token);
									if(file!=null&&file.size()>0) {
					      	    		fileInfo.add(file.get(0));
					      	    	}
								}	
							}
						}
						map.put("attJson", fileInfo);
					}
				}
				return userList;
			}
		}
		return new ArrayList<>();
	}
	
	
	/**
	 * pc端根据题目查询答题详情
	 * @param propertyFilter 
	 * @param pageable 
	 * @param quesId
	 * @param quesType
	 * @param topicId
	 * @param string 
	 * @return
	 */
	public Page<Map> getPCAnswerDetail(Pageable pageable, List<PropertyFilter> propertyFilter, String quesId,String topicType,String topicId){
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT oa.user_id as userId,su.user_name as userName" +
				" FROM oa_questionnaire_answer oa " +
				" left join sys_user su on su.id = oa.user_id"+
				" left join oa_qusetionnaire_answer_status oqas on oqas.questionnaire_id = oa.questionnaire_id and oa.user_id = oqas.createdBy"+
				" where oa.topic_id = '"+topicId+"' and oa.questionnaire_id = '" + quesId + "'"+
				" and oqas.status = '" + QusetionnaireAnswerStatus.StatusEnum.SUBMIT.getCode() + "' GROUP BY oa.user_id,su.user_name");
		return super.findByPageNativeSQL(pageable, sql.toString(), new HashMap());
	}
	
	/**
	 * 移动端根据题目查询答题人员
	 * @param propertyFilter 
	 * @param pageable 
	 * @param quesId
	 * @param quesType
	 * @param topicId
	 * @param string 
	 * @return
	 */
	public List<Map> getMBAnswerDetail(String quesId,String topicType,String topicId){
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT distinct oa.user_id as userId,su.user_name,oa.fraction" +
				" FROM oa_questionnaire_answer oa " +
				" left join sys_user su on su.id = oa.user_id"+
				" left join oa_qusetionnaire_answer_status oqas on oqas.questionnaire_id = oa.questionnaire_id and oa.user_id = oqas.createdBy"+
				" where oa.topic_id = '"+topicId+"' and oa.questionnaire_id = '" + quesId + "'"+
				" and oqas.status = '" + QusetionnaireAnswerStatus.StatusEnum.SUBMIT.getCode() + "' order by su.user_name desc");
		List<Map> list = super.findByListNativeQuery(sql.toString(), "",new HashMap());
		return list;
	}
	
	/**
	 * 根据用户和题目查询填空题选项答案
	 * @param userId 
	 * @param topicId
	 * @return
	 */
	public List<Map> getOptionAndAnswer(String userId, String topicId) {
		StringBuffer sql = new StringBuffer();
		sql.append(" select oqto.name as optionName,oqa.answer,oqa.fraction from oa_questionnaire_answer oqa");
		sql.append(" left join oa_questionnaire_topic_options oqto on oqto.id = oqa.option_id");
		sql.append(" left join oa_qusetionnaire_answer_status oqas on oqas.questionnaire_id = oqa.id and oqas.status = '" + QusetionnaireAnswerStatus.StatusEnum.SUBMIT.getCode() + "'");
		sql.append(" where oqa.user_id ='"+userId+"' and oqa.topic_id ='"+topicId+"'");
		List<Map> list = super.findByListNativeQuery(sql.toString(), "",new HashMap());
		return list;
	}
	
	/**
	 * 查询答题得分的人
	 * @param questionnaireId
	 * @param topId
	 * @param fraction
	 * @return
	 */
	public List<Map> getAnswerRightUserIds(String questionnaireId, String topId, float fraction){
		Map<String, Object> paramMap = new HashMap<String, Object>();
		StringBuffer sql = new StringBuffer();
		sql.append("select oqa.user_id from oa_questionnaire_answer oqa");
		sql.append(" left join oa_qusetionnaire_answer_status oqas on oqa.user_id=oqas.createdBy");
		sql.append(" and oqa.questionnaire_id=oqas.questionnaire_id");
		sql.append(" where oqa.questionnaire_id=:questionnaireId");
		sql.append(" and oqa.topic_id=:topId");
		sql.append(" and oqa.fraction=:fraction and oqas.status='1'");
		sql.append(" group by oqa.user_id");
		paramMap.put("questionnaireId", questionnaireId);
		paramMap.put("topId", topId);
		paramMap.put("fraction", fraction);
		return super.findByListNativeQuery(sql.toString(), null, paramMap);
	}
	
	/**
	 * 查询多项填空每人得分
	 * @param questionnaireId
	 * @param topId
	 * @return
	 */
	public List<Map> getAnswerFractionByTopId(String questionnaireId, String topId){
		Map<String, Object> paramMap = new HashMap<String, Object>();
		StringBuffer sql = new StringBuffer();
		sql.append("select sum(oqa.fraction) fraction from oa_questionnaire_answer oqa");
		sql.append(" left join oa_qusetionnaire_answer_status oqas on oqa.user_id=oqas.createdBy ");
		sql.append(" and oqa.questionnaire_id=oqas.questionnaire_id");
		sql.append(" where oqa.questionnaire_id=:questionnaireId");
		sql.append(" and oqa.topic_id=:topId");
		sql.append(" and oqas.status='1'");
		sql.append(" group by oqa.user_id");
		paramMap.put("questionnaireId", questionnaireId);
		paramMap.put("topId", topId);
		return super.findByListNativeQuery(sql.toString(), null, paramMap);
	}
}